﻿using System;
using System.Collections.Generic;
using System.Globalization;
using System.Linq;
using LSEntity;
using LSUtility;
using MySql.Data.MySqlClient;

namespace LSData
{
    public class MemberData : DataBase
    {
        #region MemberQuery

        private const string MemberQuery =
            "SELECT ex.isBind,a.AccountID,a.Account,a.Password,a.Gender,a.Born,a.RegDate,a.RegIP,a.Type,a.lastlogintm,a.UpdateTS,a.UpdatedBy,a.OACEnable,"
            + " i.RealName,i.ProvinceID,i.CityID,i.Address,i.SchoolProvinceID,i.SchoolCityID,i.SchoolID,"
            + " i.EntranceYear,i.ClassNo,i.Email,i.Mobile,i.IdCard,"
            + " r.RoleID,r.Level,r.NickName,r.CurExp,r.Gold,r.Diamonds,r.Sex,af.CurriculumType"
            + " FROM account a left join account_info i on a.AccountID=i.AccountID "
            + " left join role r on a.AccountID=r.AccountID "
            + " left join account_function af on a.AccountID=af.AccountID "
            + " left join account_external_map ex on ex.Local_account_id=a.AccountId ";

        #endregion

        #region private

        private static MemberEntity MemberExtendBind(MemberEntity result)
        {
            if (result != null && result.AccountId > 0)
            {
                #region TeacherClassList

                result.ClassList = MemberGetClass(result.AccountId);

                #endregion

                #region MemberExtend

                var extend = MemberGetExtend(result.AccountId);
                if (extend != null && extend.AccountId > 0)
                {
                    extend.Head = String.IsNullOrEmpty(extend.Head) ? "48A_01.jpg" : extend.Head;
                    result.MemberExtend = extend;
                }
                if (result.Account.IndexOf("@Sanyan.com", StringComparison.Ordinal) > -1)
                    result.MemberLoginType = MemberExternalType.Sanyan;
                if (result.Account.IndexOf("@TSW.com", StringComparison.Ordinal) > -1)
                    result.MemberLoginType = MemberExternalType.TSW;

                #endregion
            }
            return result;
        }

        private static MemberExtendEntity MemberGetExtend(int accountId)
        {
            const string accountIdParam = "accountId";
            var sql = String.Format("select * from gfb_account_extend where accountId=@{0}", accountIdParam);
            return GetInfo<MemberExtendEntity>(sql, EConn, new List<MySqlParameter>
                                                               {
                                                                   MakeParameter(accountIdParam, MySqlDbType.Int32,
                                                                                 accountId)
                                                               });
        }

        private static List<MemberClassEntity> MemberGetClass(int accountId)
        {
            const string accountIdParame = "accountId";
            var sql = String.Format(@"select 
                                        c.ID as ClassId,
                                        c.Name as ClassName,
                                        c.School_ID as SchoolId,
                                        c.Class_Group_ID as GradeId 
                                        from account_class_mapping as acm 
                                        join class as c on acm.Class_ID=c.ID 
                                        where acm.Account_ID=@{0}", accountIdParame);
            return GetList<MemberClassEntity>(sql, EConn, new List<MySqlParameter>
                                                              {
                                                                  MakeParameter(accountIdParame, MySqlDbType.Int32,
                                                                                accountId)
                                                              }).ToList();
        }

        #endregion

        public static MemberEntity MemberGet(string account)
        {
            const string accountParame = "account";
            var sql = String.Format("{0} WHERE a.Account=@{1}", MemberQuery, accountParame);
            var result = GetInfo<MemberEntity>(sql, AConn, new List<MySqlParameter>
                                                               {
                                                                   MakeParameter(accountParame, MySqlDbType.String,
                                                                                 account)
                                                               });
            return MemberExtendBind(result);
        }

        public static MemberEntity MemberGet(int accountId)
        {
            const string accountIdParame = "AccountId";
            var sql = String.Format("{0} WHERE a.AccountID=@{1}", MemberQuery, accountIdParame);
            var result = GetInfo<MemberEntity>(sql, AConn, new List<MySqlParameter>
                                                               {
                                                                   MakeParameter(accountIdParame, MySqlDbType.Int32,
                                                                                 accountId)
                                                               });
            return MemberExtendBind(result);
        }

        public static bool MemberModiby(
            int accountId,
            string email = null,
            DateTime? born = null,
            string realName = null,
            short? gender = null,
            string mobile = null)
        {
            const string emailParame = "Email";
            const string bornParame = "Born";
            const string realNameParame = "RealName";
            const string genderParame = "Gender";
            const string mobileParame = "Mobile";
            var parames1 = new List<MySqlParameter>();
            var parames2 = new List<MySqlParameter>();
            var sql1 = "update account set {0} where AccountID={1}";
            var sql2 = "update account_info set {0} where AccountID={1}";
            string set1 = String.Empty, set2 = String.Empty;

            #region email

            if (null != email)
            {
                set1 += String.Format(",Email=@{0}", emailParame);
                set2 += String.Format(",Email=@{0}", emailParame);
                parames1.Add(MakeParameter(emailParame, MySqlDbType.String, email));
                parames2.Add(MakeParameter(emailParame, MySqlDbType.String, email));
            }

            #endregion

            #region born

            if (null != born)
            {
                set1 += String.Format(",Born=@{0}", bornParame);
                parames1.Add(MakeParameter(bornParame, MySqlDbType.DateTime, born.Value));
            }

            #endregion

            #region RealName

            if (null != realName)
            {
                set2 += String.Format(",RealName=@{0}", realNameParame);
                parames2.Add(MakeParameter(realNameParame, MySqlDbType.String, realName));
            }

            #endregion

            #region Gender

            if (null != gender)
            {
                set1 += String.Format(",Gender=@{0}", genderParame);
                parames1.Add(MakeParameter(genderParame, MySqlDbType.Int16, gender.Value));
            }

            #endregion

            #region Mobile

            if (null != mobile)
            {
                set2 += String.Format(",Mobile=@{0}", mobileParame);
                parames2.Add(MakeParameter(mobileParame, MySqlDbType.String, mobile));
            }

            #endregion

            #region result

            bool result1 = false, result2 = false;
            if (set1.IndexOf(",", StringComparison.Ordinal) == 0)
            {
                set1 = set1.Substring(1);
                sql1 = String.Format(sql1, set1, accountId);
                result1 = ExecSql(sql1, AConn, parames1) > 0;
            }
            if (set2.IndexOf(",", StringComparison.Ordinal) == 0)
            {
                set2 = set2.Substring(1);
                sql2 = String.Format(sql2, set2, accountId);
                result2 = ExecSql(sql2, AConn, parames2) > 0;
            }

            #endregion

            return result1 && result2;
        }

        private static bool MemberUpdateLastTime(int accountId)
        {
            var sql = String.Format("update account set LastLoginTM='{0}' where AccountId={1}", DateTime.Now, accountId);
            return ExecSql(sql, AConn) > 0;
        }

        #region 检查用户名及密码
        /// <summary>
        /// 检查用户名及密码
        /// </summary>
        /// <param name="account">用户名</param>
        /// <param name="pwd">MD5密文密码</param>
        /// <returns></returns>
        public static bool MemberAuthCheck(string account, string pwd)
        {
            const string accountParame = "account", passwordParame = "password";
            var sql = String.Format("SELECT AccountID FROM account WHERE Account=@{0} AND Password=@{1}",
                                    accountParame,
                                    passwordParame);
            var member = GetInfo<MemberEntity>(sql, AConn, new List<MySqlParameter>
                                                               {
                                                                   MakeParameter(accountParame, MySqlDbType.String,
                                                                                 account),
                                                                   MakeParameter(passwordParame, MySqlDbType.String, pwd)
                                                               });
            var isOk = false;
            if (member != null && member.AccountId > 0)
            {
                isOk = true;
                MemberUpdateLastTime(member.AccountId);
                //sql = String.Format("update account set LastLoginTM='{0}' where Account=@{1}",
                //                    DateTime.Now.ToString(CultureInfo.InvariantCulture),
                //                    accountParame);
                //ExecSql(sql, AConn, new List<MySqlParameter>
                //                        {
                //                            MakeParameter(accountParame, MySqlDbType.String, account)
                //                        });
            }
            return isOk;
        }
        #endregion

        public static List<MemberEntity> MemberGetList(int classId)
        {
            var sql = String.Format("select Account_ID as id from account_class_mapping where Class_ID={0}",
                                    classId);
            var ids = GetList<EntityId>(sql, EConn).ToList();
            sql = String.Format("{0} where a.AccountID in({1})", MemberQuery, ids.List2Str(m => m.Id));
            var result = GetList<MemberEntity>(sql, AConn).ToList();
            result.ForEach(m =>
                               {
                                   var n = MemberExtendBind(m);
                                   m.ClassList = n.ClassList;
                                   m.MemberExtend = n.MemberExtend;
                               });
            return result;
        }
    }
}
